using System;
using Avanzis.QueryFramework;
using NUnit.Framework;

namespace Avanzis.QueryBuilder.NUnit
{
	/// <summary>
	/// From Clause Test
	/// </summary>
	[TestFixture]
	public class FromClauseTest
	{
        [TestFixtureSetUp]
        public void Setup()
        {
            QueryFrameworkConfiguration.Dialect = Dialects.MSSql2005Dialect;
            QueryProcessor.ResetProcessor();
        }

	    [Test]
        [ExpectedException(typeof(ArgumentNullException))]
        public void EmptyFrom()
        {
            FromClause from = new FromClause();
            from.ToString();
        }


		[Test]
		public void SimpleFrom()
		{
			FromClause from = new FromClause();			
			from.TableName = "Customers";
			Assert.AreEqual("FROM [Customers]", from.ToString());			

			from = new FromClause("Customers");			
			Assert.AreEqual("FROM [Customers]", from.ToString());

            from = new FromClause("Customers AS c");
            Assert.AreEqual("Customers", from.TableName);
            Assert.AreEqual("c", from.Alias);
			
			from = new FromClause();			
			from.TableName = "Customers";
			from.Alias = "c";
			Assert.AreEqual("FROM [Customers] AS c", from.ToString());	
		
			from = new FromClause("Customers", "c");			
			Assert.AreEqual("FROM [Customers] AS c", from.ToString());
			
		}

		[Test]
		public void SimpleTableJoin()
		{
			FromClause from = new FromClause("Customers");		
			from.Joins.Add(JoinTypes.Inner, "Invoices", "Customers.CustomerId = Invoices.CustomerId");
			string result = "FROM [Customers] INNER JOIN [Invoices] ON ((Customers.CustomerId = Invoices.CustomerId))";
			Assert.AreEqual(result, from.ToString());

			from = new FromClause();		
			from.TableName = "Customers";
			from.Alias = "c";
			from.Joins.Add(JoinTypes.Inner, "Invoices", "Customers.CustomerId = Invoices.CustomerId");
			result = "FROM [Customers] AS c INNER JOIN [Invoices] ON ((Customers.CustomerId = Invoices.CustomerId))";
			Assert.AreEqual(result, from.ToString());

			from = new FromClause("Customers", "c");		
			from.Joins.Add(JoinTypes.Inner, "Invoices", "Customers.CustomerId = Invoices.CustomerId");
			result = "FROM [Customers] AS c INNER JOIN [Invoices] ON ((Customers.CustomerId = Invoices.CustomerId))";
			Assert.AreEqual(result, from.ToString());

			from = new FromClause("Customers");
			from.Joins.Add(JoinTypes.Left, "Invoices", "Customers.CustomerId", SqlOperator.Equals, "Invoices.CustomerId");
			result = "FROM [Customers] LEFT JOIN [Invoices] ON ((Customers.CustomerId = Invoices.CustomerId))";
			Assert.AreEqual(result, from.ToString());     


		}

        [Test]
        public void TestJoinTables()
        {
            FromClause from = new FromClause("Customers");
            JoinTable joinTable = new JoinTable("Invoices", "Customers.CustomerId = Invoices.CustomerId");
            from.Joins.Add(joinTable);
            string result = "FROM [Customers] INNER JOIN [Invoices] ON ((Customers.CustomerId = Invoices.CustomerId))";
            Assert.AreEqual(result, from.ToString());
            Assert.AreEqual(true, from.Joins.Contains(joinTable));
            Assert.AreEqual(0, from.Joins.IndexOf(joinTable));
            from.Joins.Remove(joinTable);
            Assert.AreEqual(false, from.Joins.Contains(joinTable));
            result = "FROM [Customers]";
            Assert.AreEqual(result, from.ToString());

            JoinTable joinTable2 = new JoinTable("Providers", "Providers.ProviderId = Invoices.ProvicerId");
            from.Joins.Add(joinTable2);
            from.Joins.Insert(0, joinTable);
            result = "FROM [Customers] INNER JOIN [Invoices] ON ((Customers.CustomerId = Invoices.CustomerId)) INNER JOIN [Providers] ON ((Providers.ProviderId = Invoices.ProvicerId))";
            Assert.AreEqual(result, from.ToString());

            from.Joins.Remove(joinTable);
            from.Joins[0] = joinTable;
            result = "FROM [Customers] INNER JOIN [Invoices] ON ((Customers.CustomerId = Invoices.CustomerId))";
            Assert.AreEqual(result, from.ToString());

        }

		[Test] 
		public void MultipleJoinConditions()
		{
			FromClause from = new FromClause("Articles");		
			JoinTable jt = from.Joins.Add("ArticleFamilies", "Articles.ArticleFamilyId = ArticleFamilies.ArticleFamilyId");			
			jt.Filters.Add("Articles.ArticleType = ArticleFamilies.ArticleType");

			string result = "FROM [Articles] " +
				"INNER JOIN [ArticleFamilies] ON " + 
				"((Articles.ArticleFamilyId = ArticleFamilies.ArticleFamilyId) " + 
				"AND (Articles.ArticleType = ArticleFamilies.ArticleType))";

			Assert.AreEqual(result, from.ToString());		

			jt.SearchCondition.DefaultFiltersUnion = FilterUnion.And;

			result = "FROM [Articles] " +
				"INNER JOIN [ArticleFamilies] ON " + 
				"((Articles.ArticleFamilyId = ArticleFamilies.ArticleFamilyId) " + 
				"AND (Articles.ArticleType = ArticleFamilies.ArticleType))";

			Assert.AreEqual(result, from.ToString());		

		}

		[Test]
		public void MultipleJoins()
		{
			FromClause from = new FromClause("Articles");		
			from.Joins.Add("ArticleFamilies", "Articles.ArticleFamilyId = ArticleFamilies.ArticleFamilyId");
			from.Joins.Add("Manufacturers", "Articles.ManufacturerId = Manufacturers.ManufacturerId");
			from.Joins.Add(JoinTypes.Left, "WarehouseMovements", "Articles.ArticleId = WarehouseMovements.ArticleId");

			string result = "FROM [Articles] " +
				"INNER JOIN [ArticleFamilies] ON ((Articles.ArticleFamilyId = ArticleFamilies.ArticleFamilyId)) " + 
				"INNER JOIN [Manufacturers] ON ((Articles.ManufacturerId = Manufacturers.ManufacturerId)) " + 
				"LEFT JOIN [WarehouseMovements] ON ((Articles.ArticleId = WarehouseMovements.ArticleId))";

			Assert.AreEqual(result, from.ToString());		
			
		}

		[Test]
		public void MultipleTableJoins()
		{				


		}

		[Test]
		public void SimpleTableSourceTest()
		{
			TableSource st = new TableSource("Customer");
			Assert.AreEqual("[Customer]", st.ToString());

			st = new TableSource("Customer", "c");
			Assert.AreEqual("[Customer] AS c", st.ToString());

            st = new TableSource("(SELECT * FROM People)");
            Assert.AreEqual("(SELECT * FROM People)", st.ToString());

            st = new TableSource("(SELECT * FROM People as P) as People");

            Assert.AreEqual("(SELECT * FROM People as P)", st.TableName);
            Assert.AreEqual("People", st.Alias);
            
		}

        [Test]
        [ExpectedException(typeof(ArgumentNullException))]
        public void EmptyTableSource()
        {
            TableSource ts = new TableSource();
            ts.ToString();
        }

		[Test]
		public void JoinedTableSource()
		{			
			JoinTable jts = new JoinTable(JoinTypes.Inner, "Invoices", "Customers.CustomerId = Invoices.CustomerId");					
			// jts.Filters.Add(new SearchFilter("Customers.CustomerId", SqlOperator.Equals, "Invoices.CustomerId"));
			

			string result = "INNER JOIN [Invoices] ON ((Customers.CustomerId = Invoices.CustomerId))";
            Assert.AreEqual(result, jts.ToString());           			

		}

        [Test]
        public void SimpleJoinWithAlias()
        {
            FromClause from = new FromClause("Customers", "c");
            from.Joins.Add(JoinTypes.Inner, "Users", "u", "c.UserId", SqlOperator.Equals, "u.Id");
            
            string result = "FROM [Customers] AS c INNER JOIN [Users] AS u ON ((c.UserId = u.Id))";

            Assert.AreEqual(result, from.ToString());           		
        }

        [Test]
        public void MultipleJoinsWithSameTable()
        {
            FromClause from = new FromClause("Question", "q");
            from.Joins.Add(JoinTypes.Left, "Users", "u", "q.UserId", SqlOperator.Equals, "u.Id");
            from.Joins.Add(JoinTypes.Left, "Users", "e", "q.ExpertId", SqlOperator.Equals, "e.Id");

            string result = "FROM [Question] AS q LEFT JOIN [Users] AS u ON ((q.UserId = u.Id)) LEFT JOIN [Users] AS e ON ((q.ExpertId = e.Id))";

            Assert.AreEqual(result, from.ToString());
        }


	}
}
